import numpy as np
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
from keras.models import Sequential
from keras.layers import Dense, SimpleRNN


# 循环神经网络模型
# 读取excel指定列的数据
def read_column_from_excel(file_path, sheet_name, column_name):
    df = pd.read_excel(file_path, sheet_name=sheet_name)
    return df[column_name].tolist()


class WinePredictor:
    def __init__(self, file_path, sheet_name):
        self.file_path = file_path
        self.sheet_name = sheet_name
        self.sc = StandardScaler()
        self.model = Sequential()
        self.model.add(SimpleRNN(32, return_sequences=True, input_shape=(161, 1)))
        self.model.add(SimpleRNN(16))
        self.model.add(Dense(1, activation='sigmoid'))
        self.model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])

    def read_column_from_excel(self, column_name):
        df = pd.read_excel(self.file_path, sheet_name=self.sheet_name)
        return df[column_name].tolist()

    def train(self):
        listA = []
        listB = []
        for i in range(1, 8):
            listA.append(self.read_column_from_excel('A' + str(i)))
        arrA = np.insert(listA, 0, 1, axis=1)
        for i in range(17, 26):
            listB.append(self.read_column_from_excel('F' + str(i)))
        arrB = np.insert(listB, 0, 0, axis=1)
        arrC = np.concatenate((arrA, arrB), axis=0)
        X = arrC[:, 1:162]
        y = arrC[:, 0]
        X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
        self.sc.fit(X_train)
        X_train_std = self.sc.transform(X_train)
        X_test_std = self.sc.transform(X_test)
        X_train_std = X_train_std.reshape(-1, 161, 1)
        X_test_std = X_test_std.reshape(-1, 161, 1)
        self.model.fit(X_train_std, y_train, epochs=10, batch_size=5)

    def predict(self, X):
        X_std = self.sc.transform(X)
        X_std = X_std.reshape(-1, 161, 1)
        pred = self.model.predict(X_std)
        pred_float = pred.item()
        pred_rounded = round(pred_float, 5)  # 将预测结果四舍五入到五位小数
        print('预测结果（1：七年年限，0：10年年限）： ', pred_rounded)
        return pred_rounded


# 引用这个训练的模型
predictor = WinePredictor(r'紫外光谱扫描数据_向量_年份顺序.xlsx', 'Sheet1')
predictor.train()
# 对数据进行预测，可以随意更改读取的数据内容
data = np.array(read_column_from_excel(r'紫外光谱扫描数据_向量_年份顺序.xlsx', 'Sheet1', 'C11'))
dataA = data.reshape(1, -1)
pred = predictor.predict(dataA)
